﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.Utils.Menu;
using DevExpress.XtraEditors;
using ERP_RORZE_DLL.Product.DAO;
using ERP_RORZE_DLL.Product.Base;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_PERMISSION.Base;
using ERP_RORZE_PRODUCT.Product.BusOder;
using ERP_RORZE_SYSTEM;
using Utilities = ERP_RORZE_TOOL.Common.Utilities;

namespace ERP_RORZE_PRODUCT.Product.MO
{
    public partial class ctrlMOOption : DevExpress.XtraEditors.XtraUserControl
    {
        ClsMODAO clsMODAO = new ClsMODAO();
        DataTable dt;
        ClsMOOptionTable b = new ClsMOOptionTable();
        DXPopupMenu menu;
        private LogUtils log;

        public ctrlMOOption()
        {
            InitializeComponent();
            log = new LogUtils();

            InitPopupMenu();
            dropbtExport.DropDownControl = menu;
        }

        void Reload(object sender, EventArgs e)
        {
            try
            {

                dt = clsMODAO.GetAllMOOption();
                gridControl1.DataSource = dt;
                gridView1.BestFitColumns();
            }
            catch
            {
                MessageBox.Show("Không thể load được dữ liệu");
            }

        }
        private void ctrlMOOption_Load(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            clsConstantsFuction.ExportToExcel(gridView1, 1, true);
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            frm_MOOptionEditThis frmEdit = new frm_MOOptionEditThis();
            frmEdit.clampTime = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ClampTime").ToString();
            frmEdit.deadline = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Deadline").ToString();
            frmEdit.finishdate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Finishdate").ToString();
            frmEdit.finished = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Finished").ToString();
            frmEdit.finishMachine = "";
            frmEdit.jigID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "JigType").ToString();
            frmEdit.lastOption = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "LastOption").ToString();
            frmEdit.machineID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MachineID").ToString();
            frmEdit.mONo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MONo").ToString();
            frmEdit.mOQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MOQty").ToString();
            frmEdit.note = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Note").ToString();
            frmEdit.optionID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "OptionID").ToString();
            frmEdit.partID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID").ToString();
            frmEdit.proTime = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ProTime").ToString();
            frmEdit.shift = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Shift").ToString();
            frmEdit.ID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString();
            frmEdit.Show();
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            b.RecordNo = int.Parse(gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString());
            clsMODAO.DeleteMOOption(b);

            //write Log
            log.Table = LogConstant.Table.Product.MOOption;
            log.Task = LogConstant.Task.Delete;
            log.Description = "MONo: " + b.MONo + "; OptionID: " + b.OptionID + "; PartID: " + b.PartID;
            log.WriteLog();

            MessageBox.Show("Đã xóa");
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            btDelete_Click(sender, e);
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            btEdit_Click(sender, e);
        }

        private void btAddThis_Click(object sender, EventArgs e)
        {
            frm_MOOptionAddThis frmEdit = new frm_MOOptionAddThis();
            frmEdit.deadline = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Deadline").ToString();
            frmEdit.mONo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MONo").ToString();
            frmEdit.mOQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MOQty").ToString();
            frmEdit.partID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID").ToString();
            frmEdit.Show();
            frmEdit.Activate();
        }

        private void addWithThisToolStripMenuItem_Click(object sender, EventArgs e)
        {
            btAddThis.PerformClick();
        }

        private void ImportData()
        {
            INISettings mySetting = new INISettings();
            SqlConnection myConnection = new SqlConnection(mySetting.SQLConnectionString);

            int countOk = 0;
            int countDup = 0;
            int countErr = 0;

            List<Utilities.ListItem> listDup = new List<Utilities.ListItem>();
            List<Utilities.ListItem> listErr = new List<Utilities.ListItem>();
            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    Control parent = progressPanel1.Parent;
                    progressPanel1.Location =
                        new Point(parent.Bounds.X + parent.Bounds.Width / 2 - progressPanel1.Width / 2,
                                  parent.Bounds.Y + parent.Bounds.Height / 2 - progressPanel1.Height / 2);
                    progressPanel1.Visible = true;
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                   importFile.FileName +
                                                   ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd =
                        new OleDbCommand("Select [MONo],[PartID],[MOQty],[Deadline] ,[MachineID] " +
                                         ",[OptionID] ,[LastOption] ,[JigType] ,[ProTime],[ClampTime],[Finished]" +
                                         ",[Finishdate],[Shift],[Note] from [Sheet1$]",
                                         excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();
                    int line = 1;

                    string MONo = string.Empty;
                    string PartID = string.Empty;
                    string MOQty = string.Empty;
                    string Deadline = string.Empty;
                    string MachineID = string.Empty;
                    string OptionID = string.Empty;
                    string LastOption = string.Empty;
                    string JigType = string.Empty;
                    string ProTime = string.Empty;
                    string ClampTime = string.Empty;
                    string Finished = string.Empty;
                    string Finishdate = string.Empty;
                    string Shift = string.Empty;
                    string Note = string.Empty;


                    myConnection.Open();
                    while (dReader.Read())
                    {
                        bool exist = false;
                        line++;

                        //check null
                        try
                        {
                            if (dReader["MONo"].ToString().Trim().Equals(string.Empty)
                                && dReader["PartID"].ToString().Trim().Equals(string.Empty)
                                && dReader["MOQty"].ToString().Trim().Equals(string.Empty)
                                && dReader["Deadline"].ToString().Trim().Equals(string.Empty)
                                && dReader["MachineID"].ToString().Trim().Equals(string.Empty)
                                && dReader["OptionID"].ToString().Trim().Equals(string.Empty)
                                && dReader["LastOption"].ToString().Trim().Equals(string.Empty)
                                && dReader["JigType"].ToString().Trim().Equals(string.Empty)
                                && dReader["ProTime"].ToString().Trim().Equals(string.Empty)
                                && dReader["ClampTime"].ToString().Trim().Equals(string.Empty)
                                && dReader["Finished"].ToString().Trim().Equals(string.Empty)
                                && dReader["Finishdate"].ToString().Trim().Equals(string.Empty)
                                && dReader["Shift"].ToString().Trim().Equals(string.Empty)
                                && dReader["Note"].ToString().Trim().Equals(string.Empty)
                                )
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"], "Null line"));
                                exist = true;
                            }
                        }
                        catch (Exception e)
                        {
                            countErr++;
                            listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"], e.Message));
                            exist = true;
                        }

                        //check valid data
                        if (!exist)
                        {
                            try
                            {

                                Deadline = (dReader["Deadline"].ToString().Trim().Equals(string.Empty))
                                                ? "null"
                                                : "'" + DateTime.Parse(dReader["Deadline"].ToString().Trim()).ToString("yyyy/MM/dd") + "'";
                                Finishdate = (dReader["Finishdate"].ToString().Trim().Equals(string.Empty))
                                                ? "null"
                                                : "'" + DateTime.Parse(dReader["Finishdate"].ToString().Trim()).ToString("yyyy/MM/dd") + "'";

                                MONo = (dReader["MONo"].ToString().Trim().Equals(string.Empty))
                                             ? "null"
                                             : "'" + dReader["MONo"].ToString().Trim() + "'";

                                PartID = (dReader["PartID"].ToString().Trim().Equals(string.Empty))
                                             ? "null"
                                             : "'" + dReader["PartID"].ToString().Trim() + "'";

                                MOQty = (dReader["MOQty"].ToString().Trim().Equals(string.Empty))
                                             ? "null"
                                             :  dReader["MOQty"].ToString().Trim();
                                MachineID = (dReader["MachineID"].ToString().Trim().Equals(string.Empty))
                                                ? "null"
                                                : "N'" + dReader["MachineID"].ToString().Trim() + "'";
                                OptionID = (dReader["OptionID"].ToString().Trim().Equals(string.Empty))
                                               ? "null"
                                               : "N'" + dReader["OptionID"].ToString().Trim() + "'";
                                LastOption = (dReader["LastOption"].ToString().Trim().Equals(string.Empty))
                                                 ? "null"
                                                 : "'" + dReader["LastOption"].ToString().Trim() + "'";

                                JigType = (dReader["JigType"].ToString().Trim().Equals(string.Empty))
                                              ? "null"
                                              : "N'" + dReader["JigType"].ToString().Trim() + "'";

                                ProTime = (dReader["ProTime"].ToString().Trim().Equals(string.Empty))
                                              ? "null"
                                              : dReader["ProTime"].ToString().Trim();
                                ClampTime = (dReader["ClampTime"].ToString().Trim().Equals(string.Empty))
                                                ? "null"
                                                : dReader["ClampTime"].ToString().Trim();
                                Finished = (dReader["Finished"].ToString().Trim().Equals(string.Empty))
                                               ? "null"
                                               : "'" + dReader["Finished"].ToString().Trim() + "'";
                                Shift = (dReader["Shift"].ToString().Trim().Equals(string.Empty))
                                              ? "null"
                                              : "'" + dReader["Shift"].ToString().Trim() + "'";
                                Note = (dReader["Note"].ToString().Trim().Equals(string.Empty))
                                           ? "null"
                                           : "N'" + dReader["Note"].ToString().Trim() + "'";

                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(),
                                                                   dReader["MONo"] + " | " + dReader["OptionID"],
                                                                   ex.Message));
                            }
                        }


                        //check OptionID in [242_Option]?
                        if (!exist)
                        {

                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_Option] WHERE [OptionID]='" +
                                                      dReader["OptionID"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (!dataReader.Read())
                                {

                                    exist = true;
                                    countErr++;
                                    listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"],
                                                                       "Invalid OptionID"));
                                    //lineNo += line.ToString() + ", ";
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"],
                                                                   ex.Message));
                            }
                        }

                        //check MachineID in [222_Machine]?
                        if (!exist)
                        {

                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [222_Machine] WHERE [MachineID]='" +
                                                      dReader["MachineID"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (!dataReader.Read())
                                {

                                    exist = true;
                                    countErr++;
                                    listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"],
                                                                       "Invalid MachineID"));
                                    //lineNo += line.ToString() + ", ";
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"],
                                                                   ex.Message));
                            }
                        }

                        //check duplicate
                        if (!exist)
                        {
                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_MOOption] WHERE [OptionID]='" +
                                                      dReader["OptionID"].ToString().Trim() + "' and [MONo]='" +
                                                      dReader["MONo"].ToString().Trim() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (dataReader.Read())
                                {

                                    exist = true;
                                    countDup++;
                                    listDup.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"], "Duplicate OptionID and MONo"));
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception e)
                            {
                                exist = true;
                                listDup.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"], e.Message));
                            }
                        }


                        //check PartID, MOQty and Deadline in [242_MOOption] must equal to import source
                        if (!exist)
                        {

                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_MOOption] WHERE [MONo]='" +
                                                      dReader["MONo"].ToString().Trim().Trim() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (dataReader.Read())
                                {

                                    string ogPartID = dReader["PartID"].ToString();
                                    int ogMOQty = int.Parse(dReader["MOQty"].ToString());
                                    string ogDeadline = DateTime.Parse(dReader["Deadline"].ToString().Trim()).ToString("yyyy/MM/dd");

                                    string temPartID = dataReader["PartID"].ToString();
                                    int temMOQty = int.Parse(dataReader["MOQty"].ToString());
                                    string temDeadline = DateTime.Parse(dataReader["Deadline"].ToString()).ToString("yyyy/MM/dd");

                                    if (!temPartID.Equals(ogPartID) || !temMOQty.Equals(ogMOQty) || !temDeadline.Equals(ogDeadline))
                                    {
                                        exist = true;
                                        countErr++;
                                        listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"], "Dữ liệu của PartID, MOQty hoặc Deadline không trùng khớp."));
                                    }

                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"],
                                                                   ex.Message));
                            }
                        }

                        //Insert to database
                        if (!exist)
                        {
                            try
                            {

                                string sql = "INSERT INTO [242_MOOption] ("
                                         + "[MONo],[PartID],[MOQty],[Deadline] ,[MachineID] "
                                         + ",[OptionID] ,[LastOption] ,[JigType] ,[ProTime],[ClampTime],[Finished]"
                                         + ",[Finishdate],[Shift],[Note] )"
                                         + " VALUES (" + MONo + ", " + PartID + ", " + MOQty + ","
                                         + Deadline + "," + MachineID + ", " + OptionID + ", " + LastOption + ", " + JigType + ", "
                                         + ProTime + " ," + ClampTime + ", " + Finished + ", " + Finishdate + ", " + Shift + ", " + Note + ")";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);

                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                countOk++;

                                //write Log
                                log.Table = LogConstant.Table.Product.MOOption;
                                log.Task = LogConstant.Task.Import;
                                log.Description = "MONo: " + MONo + "; OptionID: " + OptionID + "; PartID: " + PartID;
                                log.WriteLog();

                            }
                            catch (Exception ex)
                            {
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"] + " | " + dReader["OptionID"], ex.Message));
                            }

                        }
                    }
                    myConnection.Close();
                    progressPanel1.Visible = false;
                    excelConnection.Close();
                    //MessageBox.Show(i + " records have been imported. \n" + countDup.ToString() + " record(s) duplicated (Lines: " + lineDup + ")\nLines have error OptionID: " + lineNo);
                    //LoadData();
                    //write Log
                    log.Table = LogConstant.Table.Product.MOOption;
                    log.Task = LogConstant.Task.Import;
                    log.Description = string.Format(LogConstant.Message.Import, countOk.ToString(), "");
                    log.WriteLog();

                    Frm_ImportReport report = new Frm_ImportReport(countOk, listDup, listErr, "MOOption", "MONo | OptionID");
                    report.StartPosition = FormStartPosition.CenterScreen;
                    report.Show();
                    report.Activate();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    progressPanel1.Visible = false;
                    MessageBox.Show(ex.Message);
                }

            }
        }

        private void btImport_Click(object sender, EventArgs e)
        {
            ImportData();
        }

        //Export to excel button

        protected virtual void InitPopupMenu()
        {
            menu = new DXPopupMenu();
            menu.Items.Add(new DXMenuItem("Export all", new EventHandler(OnItemClickExportAll), null, null, null, null));
            menu.Items.Add(new DXMenuItem("Export selected row(s)", new EventHandler(OnItemClickExportRows), null, null, null, null));

        }
        void OnItemClickExportAll(object sender, EventArgs e)
        {
            // gridView1.
            Utilities.ExportToExcel(gridView1, 1, true);
        }
        void OnItemClickExportRows(object sender, EventArgs e)
        {
            Utilities.ExportToExcel(gridView1, 0, true);
        }

    }
}
